<template>
  <div class="sample-tutorial">
    <gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
    </gc-spread-sheets>
    <div class="options-container">
      <h3>选择文件</h3>
      <div class="option-row">
        <label>选择文件查看关联工作簿</label>
      </div>
      <div class="option-row">
        <input type="file" id="importFile" class="input">
        <input type="button" id="openButton" value="import" class="button">
      </div>
      <br>
      <h3>上传新文件更新工作簿关联单元格</h3>
      <div class="option-row"><label>Select files to update values</label></div>
      <div class="option-row">
        <table id="states-table">
          <thead>
          <tr>
            <td>文件名</td>
            <td>文件路径</td>
            <td>Update source<br>(supports .ssjson / .xlsx)</td>
          </tr>
          </thead>
          <tbody>
          <tr>
            <td>Hover</td>
            <td><input type="checkbox" checked="checked" myState="hover" myDirection="row"/></td>
            <td><input type="checkbox" checked="checked" myState="hover" myDirection="column"/></td>
          </tr>
          </tbody>
        </table>
      </div>
      <el-button type="primary" @click="handleAddMonth"> 更新为下一个月公式</el-button>
    </div>
  </div>
</template>

<script setup lang="ts">
// 多工作簿调度
import {ref, toRaw} from "vue";
import "@grapecity-software/spread-sheets-vue";
import GC from "@grapecity-software/spread-sheets";
import "@grapecity-software/spread-sheets-io";
import '@grapecity-software/spread-sheets-resources-zh';

GC.Spread.Common.CultureManager.culture("zh-cn");


let spreadNS = GC.Spread.Sheets;

// window.GC = GC;
function readJSONFromFile(input, spread, callback) {
  const file = input.files[0];
  if (file) {
    const fileName = file.name;
    const suffix = fileName.substr(fileName.lastIndexOf('.')).toLowerCase();
    if (suffix === '.xlsx') {
      spread.import(file, function () {
        callback();
      }, function (e) {
        console.log(e);
      }, {fileType: GC.Spread.Sheets.FileType.excel});
    }
  }
}

function showLinkList(spread) {
  let table = document.getElementById("states-table");
  while (table.rows.length > 1) {
    table.deleteRow(1);
  }
  spread.getExternalReferences().forEach(item => {
    const tr = document.createElement("tr");
    let td = document.createElement("td");
    td.appendChild(document.createTextNode(item.name));
    tr.appendChild(td);
    td = document.createElement("td");
    td.appendChild(document.createTextNode(item.filePath));
    tr.appendChild(td);
    td = document.createElement("td");
    const input = document.createElement("input");
    input.type = "file";
    input.onchange = function (e) {
      updateExternalLink(e, spread)
    };
    input.setAttribute("info", JSON.stringify(item));
    td.appendChild(input);
    tr.appendChild(td);
    table.appendChild(tr);
  });
}

function updateExternalLink(e, spread: GC.Spread.Sheets.Workbook) {
  let item = JSON.parse(e.target.getAttribute("info"));
  const file = e.target.files[0];
  if (file) {
    const fileName = file.name;
    const suffix = fileName.substr(fileName.lastIndexOf('.')).toLowerCase();
    if (suffix === '.xlsx') {
      const tempWorkbook = new GC.Spread.Sheets.Workbook();
      tempWorkbook.import(file, function () {
        spread.updateExternalReference(item.name, tempWorkbook.toJSON(), item.filePath);
      }, function (e) {
        console.log(e);
      }, {fileType: GC.Spread.Sheets.FileType.excel});
    }
  }
}

const spreadRef = ref<GC.Spread.Sheets.Workbook | null>(null)

function initSpread(spread: GC.Spread.Sheets.Workbook) {
  spreadRef.value = spread;
  spread.suspendPaint();
  let sheet = spread.getActiveSheet();
  let data = [
    [''],
    ['货运公司月度汇总数据表'],
    ['Student', 1, 2, 3, 4, 5],
    ['2023年1月'],
    ['2023年2月'],
  ];

  sheet.setArray(0, 1, data);
  for (let i = 3; i <= 4; i++) {
    let name = sheet.getValue(i, 1);
    for (let j = 2; j <= 6; j++) {
      sheet.setFormula(i, j, `'[${name}.xlsx]Sheet1'!A${j}`);
    }
  }
  // 获取当前工作簿的跨工作簿引用列表
  spread.getExternalReferences().forEach(item => {
    console.log(item)
    let data = {Sheet1: [["Score:"], [parseInt(String(Math.random() * 50 + 50))], [parseInt(String(Math.random() * 50 + 50))], [parseInt(String(Math.random() * 50 + 50))], [parseInt(String(Math.random() * 50 + 50))], [parseInt(String(Math.random() * 50 + 50))]]};
    spread.updateExternalReference(item.name, data, item.filePath);
  });


  sheet.setRowHeight(0, 40);
  sheet.getCell(0, 1).font('Bold 19px Arial').vAlign(spreadNS.VerticalAlign.center);

  sheet.addSpan(1, 1, 1, 8);
  sheet.getCell(1, 1).font('Bold 13px Arial')
      .hAlign(spreadNS.HorizontalAlign.center)
      .backColor('rgb(130, 188, 0)')
      .foreColor('white')
      .vAlign(spreadNS.VerticalAlign.center);

  sheet.getRange(2, 1, 1, 8).font('Bold 13px Arial')
      .backColor('rgb(244, 248, 235)')
      .vAlign(spreadNS.VerticalAlign.center)
      .borderBottom(new spreadNS.LineBorder('black', spreadNS.LineStyle.thin));
  sheet.getCell(2, 8).hAlign(spreadNS.HorizontalAlign.right);

  sheet.getRange(3, 1, 10, 8).font('12px Arial');


  sheet.getRange(14, 1, 4, 8).backColor('rgb(230,230,230)');
  sheet.getRange(14, 1, 4, 1).font('Bold 12px Arial').hAlign(spreadNS.HorizontalAlign.right);
  [110, 100, 100, 100, 100, 100, 100, 80].forEach(function (val, index) {
    sheet.setColumnWidth(index + 1, val);
  });

  //将三色刻度规则添加到规则集合
  // sheet.conditionalFormats.add3ScaleRule(
  //     spreadNS.ConditionalFormatting.ScaleValueType.lowestValue, 10, 'rgb(231,114,111)',
  //     spreadNS.ConditionalFormatting.ScaleValueType.percentile, 15, 'rgb(252,252,255)',
  //     spreadNS.ConditionalFormatting.ScaleValueType.highestValue, 20, 'rgb(122,188,129)',
  //     [new GC.Spread.Sheets.Range(3, 2, 10, 10)]);

  spread.resumePaint();
  showLinkList(spread);

  const openButton = document.getElementById('openButton');
  openButton?.addEventListener('click', function () {
    readJSONFromFile(document.getElementById("importFile"), spread, function (json) {
      showLinkList(spread);
    });
  });
}

function handleAddMonth() {
  const spread = toRaw(spreadRef.value)
  if (!spread) return
  spread.suspendPaint()
  const sheetCount = spread.getSheetCount()
  for (let i = 0; i < sheetCount; i++) {
    const sheet = spread.getSheet(i)
    // const range = sheet.getRange(3, 2, 2, 5)
    // console.log(range)
    // 更新区域内公式，符合格式为xxxx年xx月的，把月份加一
    for (let i = 3; i < 5; i++) {
      for (let j = 1; j < 6; j++) {
        let orgFormula = sheet.getFormula(i, j)
        let value = sheet.getValue(i, j)
        console.log(orgFormula)
        if (orgFormula && orgFormula.includes('月')) {
          // 获取年月中间的数字，比如现在的数据是2023年1月，那么获取到的数字就是1，把它替换为2
          const regex = /(\d{4})年(\d{1,2})月/;
          const match = orgFormula.match(regex);
          if (match) {
            const year = match[1];
            let month = parseInt(match[2], 10);
            // 将月份加一
            month += 1;
            // 如果月份超过12，则年份加一，月份变为1
            if (month > 12) {
              month = 1;
              const nextYear = parseInt(year, 10) + 1;
              orgFormula = orgFormula.replace(regex, `${nextYear}年${month}月`);
            } else {
              orgFormula = orgFormula.replace(regex, `${year}年${month}月`);
            }
            // 更新公式
            sheet.setFormula(i, j, orgFormula);
          }
        }
        if (!orgFormula && value) {
          const regex = /(\d{4})年(\d{1,2})月/;
          const match = value.match(regex);
          if (match) {
            const year = match[1];
            let month = parseInt(match[2], 10);
            // 将月份加一
            month += 1;
            // 如果月份超过12，则年份加一，月份变为1
            if (month > 12) {
              month = 1;
              const nextYear = parseInt(year, 10) + 1;
              value = value.replace(regex, `${nextYear}年${month}月`);
            } else {
              value = value.replace(regex, `${year}年${month}月`);
            }
            // 更新公式
            sheet.setValue(i, j, value);
          }
        }
      }
    }
  }
  // 获取当前工作簿的跨工作簿引用列表
  const length = spread.getExternalReferences().length
  spread.getExternalReferences().forEach((item, index) => {
    console.log(item)
    if (index === length - 1) {
      let data = {Sheet1: [["Score:"], [parseInt(String(Math.random() * 50 + 50))], [parseInt(String(Math.random() * 50 + 50))], [parseInt(String(Math.random() * 50 + 50))], [parseInt(String(Math.random() * 50 + 50))], [parseInt(String(Math.random() * 50 + 50))]]};
      spread.updateExternalReference(item.name, data, item.filePath);
    }
  });
  spread.resumePaint()

  showLinkList(spread);
}
</script>

<style scoped lang="less">
.book-dispatch {
  width: 100%;
  height: 100%;
  overflow: hidden;
}

.sample-tutorial {
  display: flex;
}

.sample-spreadsheets {
  width: 70%;
  height: 880px;
}

.options-container {
  width: 30%;
  height: 100%;
}

#states-table {
  width: 100%;
  border-collapse: collapse;
  text-align: center;
}

#states-table td {
  border: 1px solid grey;
}
</style>